﻿using System;
using System.Data;
using System.Data.SqlClient;
using Message;
using SqlOperate;
using System.Configuration;

namespace Message
{
    /// <summary>
    /// R_Message 的摘要说明
    /// </summary>
    public class RMessage : IMessage
    {
        public static string ConnectionString = ConfigurationManager.ConnectionStrings["sqlData"].ConnectionString;

        #region IMessage 成员
        /// <summary>
        /// 添加留言信息
        /// </summary>
        /// <param name="eui">留言对象</param>
        /// <returns></returns>
        public int Insert(TMessage eui)
        {
            SqlParameter[] commandParameters = new SqlParameter[]
            {                
               
                  
					new SqlParameter("@ReceiveUserID", eui.ReceiveUserID.ToString()),
					new SqlParameter("@Title", eui.Title),
					new SqlParameter("@Info", eui.Info),
					new SqlParameter("@Date", eui.Date),
				    new SqlParameter("@Nature", eui.Nature),
					new SqlParameter("@SendUserID", eui.SendUserID)

             };
            return SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "SP_C_Message_Add", commandParameters);
        }

        /// <summary>
        /// 批量留言
        /// </summary>
        /// <param name="eui">留言对象</param>
        /// <param name="strReceiveUserID">留言对象</param>
        /// <returns></returns>
        public int Insert(TMessage eui, string strReceiveUserID)
        {
            SqlParameter[] commandParameters = new SqlParameter[] {                                 
				new SqlParameter("@ReceiveUserID", strReceiveUserID),
				new SqlParameter("@Title", eui.Title),
				new SqlParameter("@Info", eui.Info),
				new SqlParameter("@Date", eui.Date),
			    new SqlParameter("@Nature", eui.Nature),
				new SqlParameter("@SendUserID", eui.SendUserID)
             };
            return SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "SP_C_Message_Add", commandParameters);
        }


        /// <summary>
        /// 修改留言信息
        /// </summary>
        /// <param name="eui">留言对象</param>
        /// <returns></returns>
        public int Update(TMessage eui)
        {
            SqlParameter[] commandParameters = new SqlParameter[]
            {
                    new SqlParameter("@ID", eui.ID),
					new SqlParameter("@AddresserID", eui.ReceiveUserID),
					new SqlParameter("@Title", eui.Title),
					new SqlParameter("@Info", eui.Info),
					new SqlParameter("@Date", eui.Date),
					new SqlParameter("@Restore", eui.Restore),
					new SqlParameter("@Read", eui.Read),
					new SqlParameter("@Nature", eui.Nature),
					new SqlParameter("@SendeeID", eui.SendUserID),
					new SqlParameter("@Trash", eui.Trash)

            };
            return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "SP_C_Message_Update", commandParameters);
        }
        /// <summary>
        /// 根据ID删除留言信息
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public int Delete(int ID)
        {
            SqlParameter[] param = new SqlParameter[] 
            {
                new SqlParameter("@strwhere", "where ID in (" + ID + ")"), 
                new SqlParameter("@strTableName", "C_Message") 
            };
            return SqlHelper.DeleteByWhere("SP_DeleteByWhere", param);

        }
        /// <summary>
        /// 得到所有留言信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetDataTable()
        {
            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strSelect", " C_Message.*,U_UserInfo.LoginName"),
                new SqlParameter("@strWhere"," ON C_Message.SendUserID = U_UserInfo.UserID and C_Message.SendUserID is not null"),
                new SqlParameter("@strTableName", " C_Message left JOIN U_UserInfo"), 
                new SqlParameter("@strOrder", " order by Date desc,ID asc")
            };
            return SqlHelper.ExecuteTable(CommandType.StoredProcedure, "SP_SelectByWhere", param);

        }

        /// <summary>
        /// 根据留言标题、留言状态、留言时间查询留言信息
        /// </summary>
        /// <param name="parameter">
        /// parameter[0]为用户ID
        /// parameter[1]为标题
        /// parameter[2]为留言状态
        /// parameter[3]为留言时间
        /// </param>
        /// <returns></returns>
        public DataTable GetDataTable(string[] parameter)
        {
            string str = "ON 1=1";
            if (parameter[0].ToString() != "")
            {
                if (parameter[0].Contains("="))
                {
                    string strID = parameter[0].Substring(0, parameter[0].IndexOf("="));
                    if (strID == "ReceiveUserID")
                        str += " and C_Message. SendUserID=U_UserInfo.UserID";
                    else
                        str += " and C_Message.ReceiveUserID=U_UserInfo.UserID";
                }
                else
                    str += " and C_Message." + parameter[0] + "=U_UserInfo.UserID ";

            }
            if (parameter[1].ToString() != string.Empty)
            {
                str = str + " and Title like '%" + parameter[1] + "%'";
            }
            if (parameter[2].ToString() != "")
            {
                str = str + " and  [Restore]=" + parameter[2];
            }
            if (parameter[3].ToString() != "")
            {
                switch (parameter[3].ToString())
                {
                    case "七天内的留言":
                        str = str + " where  datediff(day,date,getdate())<=7";
                        break;
                    case "一个月内的留言":
                        str = str + " where  datediff(DAY,date,getdate())<=30";
                        break;
                    case "三个月内的留言":
                        str = str + " where  datediff(month,date,getdate())<=3";
                        break;
                    case "三个月以上留言":
                        str = str + " where  datediff(month,date,getdate()) >3";
                        break;
                    default:
                        str = str + " where  datediff(month,date,getdate())<=3";
                        break;
                }
            }
            if (parameter[0].Contains("="))
            {
                if (parameter[3].ToString() != "")
                    str += " and C_Message." + parameter[0];
                else
                    str += " where C_Message." + parameter[0];
                SqlParameter[] param = new SqlParameter[] 
                {
                    new SqlParameter("@strSelect", " C_Message.*,U_UserInfo.LoginName"),
                    new SqlParameter("@strwhere", str),
                    new SqlParameter("@strtableName", " C_Message left JOIN U_UserInfo"), 
                    new SqlParameter("@strOrder", "order by Date desc,ID asc") 
                };
                return SqlHelper.ExecuteTable(CommandType.StoredProcedure, "SP_SelectByWhere", param);
            }
            else
            {
                SqlParameter[] param = new SqlParameter[] 
                {
                    new SqlParameter("@strSelect", " C_Message.*,U_UserInfo.LoginName"),
                    new SqlParameter("@strwhere", str),
                    new SqlParameter("@strtableName", " C_Message left JOIN U_UserInfo"), 
                    new SqlParameter("@strOrder", "order by Date desc,ID asc") 
                };
                return SqlHelper.ExecuteTable(CommandType.StoredProcedure, "SP_SelectByWhere", param);
            }


        }

        /// <summary>
        /// 根据留言标题、留言状态、留言时间查询留言信息
        /// </summary>
        /// <param name="parameter">
        /// parameter[0]为用户ID
        /// parameter[1]为标题
        /// parameter[2]为留言状态
        /// parameter[3]为留言时间
        /// intPageIndex为查询的页数
        /// intCount返回查询的总记录数
        /// </param>
        /// <returns></returns>
        public DataTable GetDataTable(string[] parameter, int intPageIndex, out int intCount)
        {
            int PageSize = 12;
            string str = " ON 1=1";
            string strWhere = "1=1";
            string table = " C_Message left JOIN U_UserInfo";
            string select = "C_Message.*,U_UserInfo.LoginName";
            if (parameter[0].ToString() != "")//parameter[0]判断是接受留言还是发送留言
            {
                if (parameter[0].Contains("="))//包含=号说明是前台调用
                {
                    string strID = parameter[0].Substring(0, parameter[0].IndexOf("="));
                    if (strID == "ReceiveUserID")
                        str += " and C_Message. SendUserID=U_UserInfo.UserID";
                    else
                        str += " and C_Message.ReceiveUserID=U_UserInfo.UserID";
                }
                else //不包含=号后台调用
                    str += " and C_Message." + parameter[0] + "=U_UserInfo.UserID ";
            }//判断留言标题是否为空
            if (parameter[1].ToString() != string.Empty)
            {
                strWhere = strWhere + " and Title like '%" + parameter[1] + "%'";
            }//判断留言状态(1.已读/0.未读/2.已经回复)
            if (parameter[2].ToString() != "")
            {
                strWhere = strWhere + " and  [Restore]=" + parameter[2];
            }//查询多长时间以内的留言
            if (parameter[3].ToString() != "")
            {
                switch (parameter[3].ToString())
                {
                    case "七天内的留言":
                        strWhere = strWhere + " and datediff(day,date,getdate())<=7";
                        break;
                    case "一个月内的留言":
                        strWhere = strWhere + " and  datediff(DAY,date,getdate())<=30";
                        break;
                    case "三个月内的留言":
                        strWhere = strWhere + " and datediff(month,date,getdate())<=3";
                        break;
                    case "三个月以上留言":
                        strWhere = strWhere + " and datediff(month,date,getdate()) >3";
                        break;
                    default:
                        strWhere = strWhere + " and datediff(month,date,getdate())<=3";
                        break;
                }
            }
            //前台调用(根据会员登录名查询自己的留言)
            if (parameter[0].Contains("="))
            {
                if (parameter[3].ToString() != "")
                    strWhere += " and C_Message." + parameter[0];
                else
                    strWhere += " and C_Message." + parameter[0];
                DataTable dt = Base.PageList.GetDataTable(strWhere, table + str);
                intCount = Convert.ToInt32(dt.Rows[0]["Total"]);
                return Base.PageList.GetDataTable(strWhere, table + str, 1, "C_Message.Date", select, PageSize, intPageIndex, 0);
            }
            //后台调用(查询所有的留言)
            else
            {
                DataTable dt = Base.PageList.GetDataTable(str, table);
                intCount = Convert.ToInt32(dt.Rows[0]["Total"]);
                return Base.PageList.GetDataTable(str, table, 1, "C_Message.Date", select, PageSize, intPageIndex, 0);
            }
        }

        /// <summary>
        /// 根据留言ID得到留言每一条信息
        /// </summary>
        /// <param name="id">留言ID</param>
        /// <returns></returns>
        public TMessage GateItem(int id)
        {
            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strWhere", "where ID=" +Convert.ToInt32( id)), 
                new SqlParameter("@strTableName", "C_Message"), 
                new SqlParameter("@strOrder", "") 
            };
            TMessage info = new TMessage();
            using (SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.StoredProcedure, "SP_SelectByWhere", param))
            {
                if (!reader.Read())
                {
                    return info;
                }
                if (reader["ID"].ToString() != "")
                {
                    info.ID = Convert.ToInt32(reader["ID"].ToString());
                }
                if (reader["ReceiveUserID"].ToString() != "")
                {
                    info.ReceiveUserID = int.Parse(reader["ReceiveUserID"].ToString());
                }
                info.Nature = reader["Nature"].ToString();
                info.Title = reader["Title"].ToString();
                info.Info = reader["Info"].ToString();
                if (reader["date"].ToString() != "")
                {
                    info.Date = Convert.ToDateTime(reader["date"].ToString());
                }
                if (reader["Restore"].ToString() != "")
                {
                    info.Restore = int.Parse(reader["Restore"].ToString());
                }
                info.Nature = reader["Nature"].ToString();
                if (reader["SendUserID"].ToString() != "")
                {
                    info.SendUserID = int.Parse(reader["SendUserID"].ToString());
                }
            }
            return info;
        }
        #endregion
    }
}